﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Maticsoft.DBUtility;

namespace MedManager
{
    public partial class ExpiredDrugsFrm : Form
    {
        public ExpiredDrugsFrm()
        {
            InitializeComponent();
        }

        private DataTable dt;

        private void BindData()
        {
            StringBuilder sql=new StringBuilder("select a.id as '编号'");
            sql.Append(",a.medid as '药品编号'");
            sql.Append(",b.name as '药品名称'");
            sql.Append(",a.productiondate as '生产日期'");
            sql.Append(",a.shelflife as '保质期'");
            sql.Append(",a.lastamount as '剩余量'");
            sql.Append(" from stockin as a,medInfo as b");
            sql.Append(" where a.medid=b.id and a.status=0 and a.lastamount>0 and datediff(month,a.productiondate,getdate())>(a.shelflife-1)");
            dt= DbHelperSQL.Query(sql.ToString()).Tables[0];
            dgvDrugList.DataSource = dt;

        }

        private void btnExcute_Click(object sender, EventArgs e)
        {
            List<CommandInfo> list = new List<CommandInfo>();
            for(int i=0;i<dt.Rows.Count;i++)
            {
                CommandInfo cmdInfo=new CommandInfo();
                StringBuilder sql = new StringBuilder("update stockin set status=1");
                sql.Append(" where id=@id");
                SqlParameter[] par=new SqlParameter[1];
                par[0] = new SqlParameter("@id", Convert.ToInt32(dt.Rows[i]["编号"]));
                cmdInfo.CommandText = sql.ToString();
                cmdInfo.Parameters = par;
                list.Add(cmdInfo);
                sql = new StringBuilder("update medInfo set inventory=inventory-@amount where id=@id");
                par=new SqlParameter[2];
                par[0] = new SqlParameter("@amount", Convert.ToInt64(dt.Rows[i]["剩余量"]));
                par[1] = new SqlParameter("@id", Convert.ToInt32(dt.Rows[i]["药品编号"]));
                cmdInfo=new CommandInfo();
                cmdInfo.CommandText = sql.ToString();
                cmdInfo.Parameters = par;
                list.Add(cmdInfo);
            }
            int result=DbHelperSQL.ExecuteSqlTran(list);
            if(result!=0)
            {
                MessageBox.Show(this, "执行完毕！");
                BindData();
            }
            
        }

        private void ExpiredDrugsFrm_Load(object sender, EventArgs e)
        {
            BindData();
        }
    }
}
